package com.wap.framework.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.wap.framework.model.Advertisement;
import com.wap.framework.pagination.PaginationHelper;

public class AdvertisementDAOImpl implements AdvertisementDAO {

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	private static String countSql = "select count(*) from dbo.wap_ad where delflag=0;";
	private static String listCurrentPage = "select * from (select *,row_number() over(order by id) as rownum from dbo.wap_ad where delflag=0) as t where t.rownum>? and t.rownum<=?";
	private static String listAdvertisement = "select * from wap_ad  where delflag=0";
	private static String insertAdvertisement = "insert into wap_ad(title,href,src,type,alive,create_time) values(?,?,?,?,?,getdate());";
	private static String editAdvertisement = "select * from wap_ad where id=?";
	private static String deleteAdvertisement = "update wap_ad set delflag=1 where id=?;";
	private static String updateAdvertisement = "update wap_ad set title=?,href=?,src=?,type=?,alive=? where id=?";

	public void deleteAdvertisement(final int[] ids) {
		BatchPreparedStatementSetter batchPstmt = new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return ids.length;
			}

			public void setValues(PreparedStatement pstmt, int i)
					throws SQLException {
				pstmt.setInt(1, ids[i]);
			}
		};
		this.jdbcTemplate.batchUpdate(deleteAdvertisement, batchPstmt);
	}

	@SuppressWarnings("unchecked")
	public Advertisement editAdvertisement(int id) {
		Object[] params = new Object[] { id };
		int[] types = new int[] { Types.INTEGER };
		List<Advertisement> ad = jdbcTemplate.query(editAdvertisement, params,
				types, new AdvertisementRowMapper());
		if (ad == null || ad.size() == 0) {
			return null;
		}
		return (Advertisement) ad.get(0);
	}

	public void insertAdvertisement(Advertisement ad) {
		Object[] params = new Object[] { ad.getTitle(), ad.getHref(),
				ad.getSource(), ad.getType(), ad.getStatus() };
		int[] types = new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.INTEGER, Types.INTEGER };

		this.jdbcTemplate.update(insertAdvertisement, params, types);
	}

	@SuppressWarnings("unchecked")
	public List<Object> listAdvertisement(int pageNum) {
		PaginationHelper ph = new PaginationHelper(pageNum);
		ph.setJdbcTemplate(jdbcTemplate);
		ph.init(countSql);

		Object[] params = new Object[] { ph.getStartIndex(), ph.getEndIndex() };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER };
		List<Advertisement> ads = jdbcTemplate.query(listCurrentPage, params,
				types, new AdvertisementRowMapper());

		List result = new ArrayList(2);
		result.add(ads);
		result.add(ph);

		return result;
	}

	public void updateAdvertisement(Advertisement ad) {
		Object[] params = new Object[] { ad.getTitle(), ad.getHref(),
				ad.getSource(), ad.getType(), ad.getStatus(), ad.getId() };
		int[] types = new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.INTEGER, Types.INTEGER, Types.INTEGER };

		this.jdbcTemplate.update(updateAdvertisement, params, types);
	}

	class AdvertisementRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			Advertisement ad = new Advertisement();
			ad.setId(rs.getInt("id"));
			ad.setTitle(rs.getString("title"));
			ad.setHref(rs.getString("href"));
			ad.setSource(rs.getString("src"));
			ad.setType(rs.getInt("type"));
			ad.setStatus(rs.getInt("alive"));
			ad.setCreateTime(rs.getTimestamp("create_time"));
			ad.setDelflag(rs.getInt("delflag"));
			return ad;
		}
	}
}
